
/**
	Author		: Rohan
	Date		: April 17th, 2005
	Description	: Saves the sort release lot details.
*/
ALTER PROC sp_save_sort_release_lot_details
(
@sort_rel_lot_id INT,
@sort_rel_id INT,
@lot_id INT,				--Lot Id at Wafer Store.
@lot_numb VARCHAR(50),
@qty_available INT,
@qty_pulled INT,
@stage VARCHAR(50),
@wafer_ids VARCHAR(100),
@out_sort_rel_lot_id INT OUT
)
AS
BEGIN

	DECLARE @wafer_id VARCHAR(3)
	DECLARE @index INT
	DECLARE @lot_id_with_sort_bin_info INT
	DECLARE @sort_release_close_flag INT
	
	SELECT @out_sort_rel_lot_id = SORT_REL_LOT_ID FROM SORT_RELEASE_LOT_TABLE WHERE SORT_REL_LOT_ID = @sort_rel_lot_id

	IF( @@rowcount = 0 )
	BEGIN
		INSERT INTO SORT_RELEASE_LOT_TABLE( SORT_REL_ID, LOT_ID, LOT_NUMBER, QTY_AVAIL, QTY_PULLED, STAGE, WAFER_Id )
					      VALUES( @sort_rel_id, @lot_id, @lot_numb, @qty_available, @qty_pulled, @stage, @wafer_ids )
	
		SET @out_sort_rel_lot_id = @@identity
	END
	ELSE
	BEGIN
		UPDATE SORT_RELEASE_LOT_TABLE SET SORT_REL_ID = @sort_rel_id,
							  LOT_ID = @lot_id,
							  LOT_NUMBER = @lot_numb,
							  QTY_AVAIL = @qty_available,
							  QTY_PULLED = @qty_pulled,
							  STAGE = @stage, 
							  WAFER_ID = @wafer_ids WHERE SORT_REL_LOT_ID = @sort_rel_lot_id	
	
	END

	SET @sort_release_close_flag = 0 -- 0 means Need to Close Sort Release.
	SET @lot_id_with_sort_bin_info = NULL

	--Bringing the Corresponding Lot Id at Sort Stage for the Lot Id at Wafer Store.
	SELECT @lot_id_with_sort_bin_info = Lot_Id FROM LOT_TABLE LT, LOCATION LN WHERE LT.LOT_NUMBER = @lot_numb AND LT.LOCATION_ID= LN.LOCATION_ID AND LN.L_TYPE = 'SORT'

	IF ( @lot_id_with_sort_bin_info IS NOT NULL AND @lot_id_with_sort_bin_info != 0 )
	BEGIN
		--Checking whether all wafers got the binning info, if not setting the flag to 1.
		WHILE ( LEN(@wafer_ids) > 0 )
		BEGIN
			IF ( CHARINDEX ( ';', @wafer_ids, 1 ) > 0 ) 
			BEGIN
				SET @index = CHARINDEX ( ';', @wafer_ids, 1 )
				SET @wafer_id = SUBSTRING( @wafer_ids, 0, @index ) 
				SET @wafer_ids = SUBSTRING( @wafer_ids, @index+1, LEN( @wafer_ids ) )
				IF ( ( SELECT COUNT(*) FROM INFORMATIVE_BINNING WHERE Lot_Id = @lot_id_with_sort_bin_info AND Wafer_Id = @wafer_id ) = 0 )
				BEGIN
					SET @sort_release_close_flag = 1				
				END
			END	
		END
	
		--Closing the This Sort Release, if all of its wafers got the binning information.
		IF ( @sort_release_close_flag = 0 )
		BEGIN
			UPDATE SORT_RELEASE_TABLE SET STATUS = 'Closed' WHERE SORT_REL_ID = @sort_rel_id
		END
	END

	--For Closing Lot at Wafer Store, if all the Wafers are released to Sort Process(i.e. Sort Release done)
	DECLARE @no_wafers_at_wafer_store INT
	DECLARE	@no_wafers_at_sort_release INT

	SET @no_wafers_at_wafer_store  = 0
	SET @no_wafers_at_sort_release = 0

	--Getting No Of Wafers at Wafer Store for The Lot
	SELECT @no_wafers_at_wafer_store = IL_WAFER_QTY_IN FROM INVENTORY_LOT WHERE LOT_ID = @lot_id

	--Getting No Of Wafers released to Sort Process (i.e. Sort Release done)
	SELECT @no_wafers_at_sort_release = ISNULL( SUM( Qty_Pulled ), 0) 
		FROM Sort_Release_Table SRT, Sort_Release_Lot_Table SRLT
		WHERE Lot_Id = @lot_id
		AND SRT.SORT_REL_ID = SRLT.SORT_REL_ID and SRT.STATUS <> 'Cancelled'

	--Modifying Lot Status at Wafer Store.
	IF ( @no_wafers_at_wafer_store = @no_wafers_at_sort_release )
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE Lot_Id = @lot_id
	
	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE set LOT_CURRENT_STATUS = 'Running' WHERE Lot_Id = @lot_id
	END
			

END --End of proc